package com.example.usermanagement.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/generateSql")
public class SqlGeneratorController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${spring.datasource.username}")
    private String dbUsername;

    @GetMapping("/fullDatabase")
    public ResponseEntity<byte[]> generateFullDatabaseSql() {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try (OutputStreamWriter writer = new OutputStreamWriter(outputStream, "UTF-8")) {
            // 1. 生成表结构
            generateTablesSql(writer);

            // 2. 生成序列
            generateSequencesSql(writer);

            // 3. 生成触发器
            generateTriggersSql(writer);

            // 4. 生成外键约束
            generateForeignKeysSql(writer);

            // 5. 生成数据
            generateDataSql(writer);

            writer.flush();

            HttpHeaders headers = new HttpHeaders();
            headers.add("Content-Disposition", "attachment; filename=user1_DesignPattern.sql");
            headers.add("Content-Type", "text/plain; charset=UTF-8");

            return ResponseEntity.ok()
                    .headers(headers)
                    .body(outputStream.toByteArray());
        } catch (IOException e) {
            e.printStackTrace();
            return ResponseEntity.status(500).body("Error generating SQL file.".getBytes());
        }
    }

    private void generateTablesSql(OutputStreamWriter writer) throws IOException {
        writer.write("-- =============================================\n");
        writer.write("-- 1. 创建表结构\n");
        writer.write("-- =============================================\n\n");

        String tablesSql = "SELECT table_name FROM all_tables WHERE owner = '" + dbUsername.toUpperCase() + "'";
        List<Map<String, Object>> tables = jdbcTemplate.queryForList(tablesSql);

        for (Map<String, Object> table : tables) {
            String tableName = (String) table.get("TABLE_NAME");

            // 获取列信息
            String columnsSql = "SELECT column_name, data_type, data_length, data_precision, " +
                    "data_scale, nullable, data_default FROM all_tab_columns " +
                    "WHERE table_name = '" + tableName + "' AND owner = '" + dbUsername.toUpperCase() + "'";
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(columnsSql);

            // 获取主键信息
            String pkSql = "SELECT cols.column_name FROM all_constraints cons, all_cons_columns cols " +
                    "WHERE cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name " +
                    "AND cons.table_name = '" + tableName + "' AND cons.owner = '" + dbUsername.toUpperCase() + "'";
            List<Map<String, Object>> primaryKeys = jdbcTemplate.queryForList(pkSql);

            // 生成CREATE TABLE语句
            writer.write("DROP TABLE " + tableName + " CASCADE CONSTRAINTS;\n");
            writer.write("CREATE TABLE " + tableName + " (\n");

            // 生成列定义
            for (int i = 0; i < columns.size(); i++) {
                Map<String, Object> column = columns.get(i);
                String columnName = (String) column.get("COLUMN_NAME");
                String dataType = (String) column.get("DATA_TYPE");
                String nullable = (String) column.get("NULLABLE");
                String defaultValue = (String) column.get("DATA_DEFAULT");

                // 处理数据类型
                String fullDataType = dataType;
                if ("NUMBER".equals(dataType)) {
                    Object precision = column.get("DATA_PRECISION");
                    Object scale = column.get("DATA_SCALE");
                    if (precision != null && scale != null) {
                        fullDataType += "(" + precision + "," + scale + ")";
                    } else if (precision != null) {
                        fullDataType += "(" + precision + ")";
                    }
                } else if ("VARCHAR2".equals(dataType) || "CHAR".equals(dataType)) {
                    fullDataType += "(" + column.get("DATA_LENGTH") + ")";
                }

                writer.write("    " + columnName + " " + fullDataType);

                if ("N".equals(nullable)) {
                    writer.write(" NOT NULL");
                }

                if (defaultValue != null) {
                    writer.write(" DEFAULT " + defaultValue);
                }

                if (i < columns.size() - 1) {
                    writer.write(",");
                }
                writer.write("\n");
            }

            // 生成主键约束
            if (!primaryKeys.isEmpty()) {
                writer.write("    ,CONSTRAINT PK_" + tableName + " PRIMARY KEY (");
                for (int i = 0; i < primaryKeys.size(); i++) {
                    writer.write(primaryKeys.get(i).get("COLUMN_NAME").toString());
                    if (i < primaryKeys.size() - 1) {
                        writer.write(", ");
                    }
                }
                writer.write(")\n");
            }

            writer.write(");\n\n");
        }
    }

    private void generateSequencesSql(OutputStreamWriter writer) throws IOException {
        writer.write("-- =============================================\n");
        writer.write("-- 2. 创建序列\n");
        writer.write("-- =============================================\n\n");

        String seqSql = "SELECT sequence_name, min_value, max_value, increment_by, " +
                "cycle_flag, order_flag, cache_size, last_number " +
                "FROM all_sequences WHERE sequence_owner = '" + dbUsername.toUpperCase() + "'";
        List<Map<String, Object>> sequences = jdbcTemplate.queryForList(seqSql);

        for (Map<String, Object> seq : sequences) {
            String seqName = (String) seq.get("SEQUENCE_NAME");
            writer.write("DROP SEQUENCE " + seqName + ";\n");
            writer.write("CREATE SEQUENCE " + seqName + "\n");
            writer.write("    START WITH " + seq.get("LAST_NUMBER") + "\n");
            writer.write("    INCREMENT BY " + seq.get("INCREMENT_BY") + "\n");
            writer.write("    MINVALUE " + seq.get("MIN_VALUE") + "\n");
            writer.write("    MAXVALUE " + seq.get("MAX_VALUE") + "\n");
            writer.write("    " + ("Y".equals(seq.get("CYCLE_FLAG")) ? "CYCLE" : "NOCYCLE") + "\n");
            writer.write("    " + ("Y".equals(seq.get("ORDER_FLAG")) ? "ORDER" : "NOORDER") + "\n");
            writer.write("    CACHE " + seq.get("CACHE_SIZE") + ";\n\n");
        }
    }

    private void generateTriggersSql(OutputStreamWriter writer) throws IOException {
        writer.write("-- =============================================\n");
        writer.write("-- 3. 创建触发器\n");
        writer.write("-- =============================================\n\n");

        String triggerSql = "SELECT trigger_name, trigger_type, triggering_event, table_name, " +
                "status, trigger_body FROM all_triggers " +
                "WHERE owner = '" + dbUsername.toUpperCase() + "'";
        List<Map<String, Object>> triggers = jdbcTemplate.queryForList(triggerSql);

        for (Map<String, Object> trigger : triggers) {
            String triggerName = (String) trigger.get("TRIGGER_NAME");
            String tableName = (String) trigger.get("TABLE_NAME");
            String triggerBody = (String) trigger.get("TRIGGER_BODY");

            writer.write("CREATE OR REPLACE TRIGGER " + triggerName + "\n");
            writer.write("    " + trigger.get("TRIGGER_TYPE") + " " + trigger.get("TRIGGERING_EVENT") + "\n");
            writer.write("    ON " + tableName + "\n");
            writer.write("    " + triggerBody.replaceAll("(?m)^", "    ") + "\n");
            writer.write("/\n\n");
        }
    }

    private void generateForeignKeysSql(OutputStreamWriter writer) throws IOException {
        writer.write("-- =============================================\n");
        writer.write("-- 4. 创建外键约束\n");
        writer.write("-- =============================================\n\n");

        String fkSql = "SELECT a.constraint_name, a.table_name, a.r_owner, a.r_constraint_name, " +
                "b.column_name, c.table_name as r_table_name, c.column_name as r_column_name " +
                "FROM all_constraints a, all_cons_columns b, all_cons_columns c " +
                "WHERE a.constraint_type = 'R' " +
                "AND a.owner = '" + dbUsername.toUpperCase() + "' " +
                "AND a.constraint_name = b.constraint_name " +
                "AND a.r_constraint_name = c.constraint_name " +
                "AND a.r_owner = c.owner";
        List<Map<String, Object>> foreignKeys = jdbcTemplate.queryForList(fkSql);

        for (Map<String, Object> fk : foreignKeys) {
            String constraintName = (String) fk.get("CONSTRAINT_NAME");
            String tableName = (String) fk.get("TABLE_NAME");
            String columnName = (String) fk.get("COLUMN_NAME");
            String rTableName = (String) fk.get("R_TABLE_NAME");
            String rColumnName = (String) fk.get("R_COLUMN_NAME");

            writer.write("ALTER TABLE " + tableName + "\n");
            writer.write("    ADD CONSTRAINT " + constraintName + "\n");
            writer.write("    FOREIGN KEY (" + columnName + ")\n");
            writer.write("    REFERENCES " + rTableName + " (" + rColumnName + ");\n\n");
        }
    }

    private void generateDataSql(OutputStreamWriter writer) throws IOException {
        writer.write("-- =============================================\n");
        writer.write("-- 5. 插入数据\n");
        writer.write("-- =============================================\n\n");

        String tablesSql = "SELECT table_name FROM all_tables WHERE owner = '" + dbUsername.toUpperCase() + "'";
        List<Map<String, Object>> tables = jdbcTemplate.queryForList(tablesSql);

        for (Map<String, Object> table : tables) {
            String tableName = (String) table.get("TABLE_NAME");

            // 获取列信息
            String columnsSql = "SELECT column_name FROM all_tab_columns " +
                    "WHERE table_name = '" + tableName + "' AND owner = '" + dbUsername.toUpperCase() + "' " +
                    "ORDER BY column_id";
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(columnsSql);

            // 获取数据
            String dataSql = "SELECT * FROM " + tableName;
            List<Map<String, Object>> rows = jdbcTemplate.queryForList(dataSql);

            if (!rows.isEmpty()) {
                writer.write("-- 数据表: " + tableName + "\n");

                for (Map<String, Object> row : rows) {
                    writer.write("INSERT INTO " + tableName + " (");
                    writer.write(String.join(", ", row.keySet()));
                    writer.write(") VALUES (");

                    int i = 0;
                    for (Object value : row.values()) {
                        if (value == null) {
                            writer.write("NULL");
                        } else {
                            // 处理特殊字符和数据类型
                            String strValue = value.toString();
                            if (value instanceof String) {
                                strValue = strValue.replace("'", "''");
                                writer.write("'" + strValue + "'");
                            } else if (value instanceof java.sql.Date || value instanceof java.sql.Timestamp) {
                                writer.write("TO_DATE('" + value + "', 'YYYY-MM-DD HH24:MI:SS')");
                            } else {
                                writer.write(strValue);
                            }
                        }

                        if (i < row.size() - 1) {
                            writer.write(", ");
                        }
                        i++;
                    }

                    writer.write(");\n");
                }
                writer.write("\n");
            }
        }
    }
}